package com.itheima.health.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.health.pojo.Member;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface MemberMapper extends BaseMapper<Member> {
    @Select("SELECT  count(tm.ID) as memberCounts, " +
            "concat(year(rp.fdate),\"-\",rp.fmonth) as yearAndMonth " +
            "from rpt_date  rp  ,t_member  tm " +
            "where rp.fdate = tm.REGTIME " +
            "and rp.fyear = year(now())-1 " +
            "group by rp.fmonth")
    List<Map> getMemberCountAndYear();

    @Select("select count(tm.ID) as memberCounts, " +
            "concat(year(rp.fdate),\"-\",rp.fmonth) as yearAndMonth " +
            "from rpt_date  rp  ,t_member  tm " +
            "where rp.fdate = tm.REGTIME  " +
            "and tm.REGTIME  " +
            "between #{startDate} and #{endDate} " +
            "group by  rp.fyear,rp.fmonth")
    List<Map> getMemberCount(@Param("startDate") String startDate, @Param("endDate") String endDate);

    @Select("select " +
            "         SUM(case when age <=18 then 1 else 0 end) as '0-18岁', " +
            "         SUM(case when age between 19 and 30 then 1 else 0 end) as '19-30岁', " +
            "         SUM(case when age between 31 and 45 then 1 else 0 end) as '31-45岁', " +
            "         SUM(case when age >=46 then 1 else 0 end) as '46岁以上' " +
            "         from  (" +
            "         select (SUBSTRING(NOW(),1,4)-SUBSTRING(IDCARD,7,4))-(SUBSTRING(IDCARD,11,4)-DATE_FORMAT(NOW(),'%m%d')>0) as age from t_member " +
            "         ) s ")
    Map countMemberBySexAndAge();
}
